# 导入所需的库
import uuid
from contextlib import asynccontextmanager
from datetime import datetime
from datetime import timedelta
from typing import List, Any
from typing import Optional
from typing import Union
from PIL import Image
from io import BytesIO
import aiomysql
import oss2
import requests
import uvicorn
from fastapi import FastAPI, Depends, HTTPException, status, File, UploadFile
from fastapi.middleware.cors import CORSMiddleware
from fastapi.responses import JSONResponse
from fastapi.security import OAuth2PasswordBearer
from jose import jwt, JWTError
from pydantic import BaseModel, Field


def convert_empty_to_none(value):
    return value if value != '' else None


# 连接池配置
pool = None


# 定义 lifespan 事件处理器进行开启和关闭数据库连接池
@asynccontextmanager
async def lifespan(app: FastAPI):
    global pool
    # 启动时创建连接池
    pool = await aiomysql.create_pool(
        host="rm-2zea30h4sh8g15zd1ho.mysql.rds.aliyuncs.com",
        port=3306,
        user="root",
        password="Ds2024@()833429",
        db="qianrenqiandian",
        minsize=3,
        maxsize=10
    )
    yield  # 应用程序运行期间保持连接池
    # 关闭时释放连接池
    if pool:
        pool.close()
        await pool.wait_closed()


# 配置 CORS（跨源资源分享）
origins = [
    "http://localhost",
    "http://127.0.0.1",
    "http://localhost:5173",
    "https://aimk.chinacpt.com"
]

methods = ["GET", "POST", "PUT", "DELETE"]  # 允许的 HTTP 方法
allow_credentials = True  # 允许发送身份验证信息（cookies）到服务器

# 创建 FastAPI 应用，并传入 lifespan 处理器
app = FastAPI(lifespan=lifespan)
# 添加 CORS 中间件到 FastAPI 应用
app.add_middleware(
    CORSMiddleware,
    allow_origins=origins,  # 允许的所有来源
    allow_credentials=allow_credentials,  # 允许发送身份验证信息
    allow_methods=methods,  # 允许的所有 HTTP 方法
    allow_headers=["*"],  # 允许的所有头部信息
)


@asynccontextmanager
async def get_db():
    async with pool.acquire() as conn:
        try:
            yield conn
            await conn.commit()  # 自动提交事务
        except Exception as e:
            await conn.rollback()
            raise


# 查询本人店铺信息接口
@app.get("/query_store/{id}")
async def query_store(id: int, page: int = 1, page_size: int = 10):
    async with get_db() as db:
        async with db.cursor(aiomysql.DictCursor) as cursor:
            # 计算偏移量
            offset = (page - 1) * page_size

            # 查询总记录数
            count_sql = """
            SELECT COUNT(*) as total_count
            FROM 店铺信息表 
            WHERE 业务员id = %s
            """
            try:
                await cursor.execute(count_sql, (id,))
                total_count_result = await cursor.fetchone()
                total_count = total_count_result['total_count']
            except Exception as e:
                raise HTTPException(status_code=500, detail=str(e))

            # 计算总页数
            total_pages = (total_count + page_size - 1) // page_size

            # 分页查询数据
            query_sql = """
            SELECT id,门店图片1, 门店图片2, 门店图片3, 经度, 纬度, 省, 市, 区,街道, 门店名称, 门店地址, 
                   负责人姓名, 门店面积, 教练人数, 会员数量, 卡费, 教练日均课数, 竞品情况, 
                   购买渠道, 合作意向, 其他, 下一步计划, 创建时间, 更新人id, 更新时间
            FROM 店铺信息表 
            WHERE 业务员id = %s order by 创建时间 desc
            LIMIT %s OFFSET %s
            """
            try:
                await cursor.execute(query_sql, (id, page_size, offset))
                results = await cursor.fetchall()
                for row in results:
                    if not row['门店图片1']:
                        row[
                            '门店图片1'] = "https://qianrenqiandian.oss-rg-china-mainland.aliyuncs.com/63726f53-abaa-40ab-8d1b-5c244cc11de7.jpg"
            except Exception as e:
                raise HTTPException(status_code=500, detail=str(e))

    # 封装分页信息和查询结果
    response_data = {
        "data": results,
        "page": page,
        "page_size": page_size,
        "total_pages": total_pages,
        "total_count": total_count
    }
    return response_data


@app.delete("/delete_store/{id}")
async def delete_store(id: int):
    print(id)
    async with get_db() as db:
        async with db.cursor() as cursor:
            delete_sql = "DELETE FROM 店铺信息表 WHERE id = %s"
            await cursor.execute(delete_sql, (id,))
            await db.commit()
            return {"message": "删除成功"}


# 定义动销明细模型
# 动销明细模型接口========================================================================================================================================================================================================
class SalesDetailItem(BaseModel):
    id: Optional[int] = None  # 主键id
    store_info_id: Optional[Union[str, float]] = None  # 店铺信息id
    month: Optional[Union[str, float]] = None  # 月份
    whey_5lb: Optional[Union[str, float]] = None  # 5磅乳清
    number_of_visits: Optional[Union[str, float]] = None  # 拜访次数
    isolate_4lb: Optional[Union[str, float]] = None  # 4磅分离
    probiotic_whey_4lb: Optional[Union[str, float]] = None  # 4磅益生菌乳清
    mass_gainer_7lb: Optional[Union[str, float]] = None  # 7磅增肌粉
    whey_900g: Optional[Union[str, float]] = None  # 900g乳清
    liquid_nitrogen_pump: Optional[Union[str, float]] = None  # 液体氮泵
    zinc_magnesium_creatine_300g: Optional[Union[str, float]] = None  # 300g锌镁肌酸
    zinc_magnesium_creatine_100g: Optional[Union[str, float]] = None  # 100g锌镁肌酸
    glutamine_100g: Optional[Union[str, float]] = None  # 100g谷氨
    bcaa_100g: Optional[Union[str, float]] = None  # 100g支链
    carnitine_11w: Optional[Union[str, float]] = None  # 左旋11w
    small_carnitine: Optional[Union[str, float]] = None  # 小左旋
    whey: Optional[Union[str, float]] = None  # 乳清
    sales_amounts: Optional[Union[str, float]] = None  # 动销金额


# 添加动销明细接口
@app.post("/add_sales_detail")
async def add_sales_detail(salesDetailItem: SalesDetailItem):
    print('接到了新增的请求')
    async with get_db() as db:
        async with db.cursor() as cursor:  # 使用异步上下文管理器创建游标
            month = salesDetailItem.month + '-01'
            values = (
                convert_empty_to_none(salesDetailItem.store_info_id),  # 店铺信息id
                month,  # 月份
                convert_empty_to_none(salesDetailItem.whey_5lb),  # 5磅乳清
                convert_empty_to_none(salesDetailItem.number_of_visits),  # 拜访次数
                convert_empty_to_none(salesDetailItem.isolate_4lb),  # 4磅分离
                convert_empty_to_none(salesDetailItem.probiotic_whey_4lb),  # 4磅益生菌乳清
                convert_empty_to_none(salesDetailItem.mass_gainer_7lb),  # 7磅增肌粉
                convert_empty_to_none(salesDetailItem.whey_900g),  # 900g乳清
                convert_empty_to_none(salesDetailItem.liquid_nitrogen_pump),  # 液体氮泵
                convert_empty_to_none(salesDetailItem.zinc_magnesium_creatine_300g),  # 300g锌镁肌酸
                convert_empty_to_none(salesDetailItem.zinc_magnesium_creatine_100g),  # 100g锌镁肌酸
                convert_empty_to_none(salesDetailItem.glutamine_100g),  # 100g谷氨
                convert_empty_to_none(salesDetailItem.bcaa_100g),  # 100g支链
                convert_empty_to_none(salesDetailItem.carnitine_11w),  # 左旋11w
                convert_empty_to_none(salesDetailItem.small_carnitine),  # 小左旋
                convert_empty_to_none(salesDetailItem.whey),  # 乳清
                convert_empty_to_none(salesDetailItem.sales_amounts)  # 动销金额
            )
            insert_sql = """INSERT INTO 动销明细表 
            (店铺信息id, 月份,5磅乳清, 拜访次数, 4磅分离, 4磅益生菌乳清, 7磅增肌粉, 900g乳清, 液体氮泵, 300g锌镁肌酸, 100g锌镁肌酸, 100g谷氨, 100g支链, 左旋11w, 小左旋, 乳清,动销金额) 
            VALUES
             (%s, %s, %s, %s,%s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                """
            log_sql = insert_sql % values
            print(log_sql)
            try:
                await cursor.execute(insert_sql, values)
                await db.commit()  # 提交事务
            except Exception as e:  # 如果插入失败，抛出 HTTP 500 错误并返回异常信息
                print(e)
                raise HTTPException(status_code=500, detail=str(e))

    return {"message": "Data added successfully"}  # 返回数据添加成功的消息


# 修改动销明细接口，使用 POST 请求
@app.post("/update_sales_detail")
async def update_sales_detail(salesDetailItem: SalesDetailItem):
    store_info_id = salesDetailItem.store_info_id
    async with get_db() as db:
        async with db.cursor() as cursor:
            salesDetailItem.month += '-01'
            print('接到了修改的请求')
            # 构建动态 SQL
            set_clauses = []
            values = []

            # 定义字段映射
            field_mapping = {
                "id": "id",
                "store_info_id": "店铺信息id",
                "month": "月份",
                "whey_5lb": "5磅乳清",
                "number_of_visits": "拜访次数",
                "isolate_4lb": "4磅分离",
                "probiotic_whey_4lb": "4磅益生菌乳清",
                "mass_gainer_7lb": "7磅增肌粉",
                "whey_900g": "900g乳清",
                "liquid_nitrogen_pump": "液体氮泵",
                "zinc_magnesium_creatine_300g": "300g锌镁肌酸",
                "zinc_magnesium_creatine_100g": "100g锌镁肌酸",
                "glutamine_100g": "100g谷氨",
                "bcaa_100g": "100g支链",
                "carnitine_11w": "左旋11w",
                "small_carnitine": "小左旋",
                "whey": "乳清",
                "sales_amounts": "动销金额"
            }

            # 遍历请求数据，构建 SET 子句
            for field, value in salesDetailItem.dict().items():
                if value is not None:
                    set_clauses.append(f"{field_mapping[field]} = %s")
                    values.append(convert_empty_to_none(value))

            # 如果没有有效的更新字段，返回错误
            if not set_clauses:
                raise HTTPException(status_code=400, detail="No valid fields to update")

            # 构建完整的 SQL 语句
            update_sql = f"""
            UPDATE 动销明细表
            SET {', '.join(set_clauses)}
            WHERE 店铺信息id = %s
            """
            values.append(store_info_id)

            try:
                await cursor.execute(update_sql, values)
                await db.commit()
                if cursor.rowcount == 0:
                    raise HTTPException(status_code=404, detail="Record not found")
            except Exception as e:
                print(e)
                raise HTTPException(status_code=500, detail=str(e))
    return {"message": "Data updated successfully"}


# 查询动销明细接口
@app.get("/get_sales_detail/{store_id}")
async def get_sales_detail(store_id: int, month: str):
    async with get_db() as db:
        async with db.cursor(aiomysql.DictCursor) as cursor:
            month = month + '-01'
            select_sql = "SELECT * FROM 动销明细表  WHERE 店铺信息id = %s and 月份=%s"
            await cursor.execute(select_sql, (store_id, month))
            result = await cursor.fetchone()

            if result is None:
                return None
            result = {
                "id": result["id"],
                "店铺信息id": result["店铺信息id"],
                "拜访次数": result["拜访次数"],
                "5磅乳清": result["5磅乳清"],
                "4磅分离": result["4磅分离"],
                "4磅益生菌乳清": result["4磅益生菌乳清"],
                "7磅增肌粉": result["7磅增肌粉"],
                "900g乳清": result["900g乳清"],
                "液体氮泵": result["液体氮泵"],
                "300g锌镁肌酸": result["300g锌镁肌酸"],
                "100g锌镁肌酸": result["100g锌镁肌酸"],
                "100g谷氨": result["100g谷氨"],
                "100g支链": result["100g支链"],
                "左旋11w": result["左旋11w"],
                "小左旋": result["小左旋"],
                "乳清": result["乳清"],
                "动销金额": result["动销金额"]
            }
            return result


# 门店信息填报接口========================================================================================================================================================================================================
class StoreAndMonthlyInfo(BaseModel):
    # 门店信息字段
    id: Optional[int] = None
    store_information_id: Optional[int] = None
    customer_category: Optional[str] = None  # 客户分类
    store_location: Optional[str] = None  # 门店位置
    financial_strength: Optional[str] = None  # 资金实力
    coach_count: Optional[str] = None  # 门店教练数量
    member_quality: Optional[str] = None  # 会员质量
    member_count: Optional[str] = None  # 会员数量
    class_fee_or_card_price: Optional[str] = None  # 门店课时费或者卡价
    familiarity_with_sports_nutrition: Optional[str] = None  # 对运动营养食品熟悉程度
    subjective_attitude: Optional[str] = None  # 主观态度
    cooperation_level: Optional[str] = None  # 配合程度
    scene: Optional[str] = None  # 场景

    # 月度动态信息字段
    monthly_id: Optional[int] = None
    month: Optional[str] = None  # 月份
    sales_quantity: Optional[str] = None  # 销售数量
    main_sales_products: Optional[str] = None  # 主要销售产品
    monthly_action_implementation: Optional[str] = None  # 每月动作落实情况
    sales_analysis: Optional[str] = None  # 动销分析


# 增加门店信息和月度动态信息接口
@app.post("/add_store_and_monthly_info")
async def add_store_and_monthly_info(info: StoreAndMonthlyInfo):
    print('接到了前端的请求')
    async with get_db() as db:
        async with db.cursor() as cursor:
            # 插入门店信息
            insert_store_sql = """
            INSERT INTO 门店信息表 
            (店铺信息id,客户分类, 门店位置, 资金实力, 门店教练数量, 会员质量, 会员数量, 门店课时费或者卡价, 对运动营养食品熟悉程度, 主观态度, 配合程度, 场景) 
            VALUES (%s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            store_values = (
                info.store_information_id, info.customer_category, info.store_location, info.financial_strength,
                info.coach_count,
                info.member_quality, info.member_count, info.class_fee_or_card_price,
                info.familiarity_with_sports_nutrition, info.subjective_attitude, info.cooperation_level, info.scene
            )
            await cursor.execute(insert_store_sql, store_values)
            # 插入月度动态信息
            insert_monthly_sql = """
            INSERT INTO 门店信息月度动态信息表 
            (月份, 店铺信息id, 销售数量, 主要销售产品, 每月动作落实情况, 动销分析) 
            VALUES (%s, %s, %s, %s, %s, %s)
            """
            monthly_values = (
                info.month + '-01', info.store_information_id, info.sales_quantity, info.main_sales_products,
                info.monthly_action_implementation, info.sales_analysis
            )
            await cursor.execute(insert_monthly_sql, monthly_values)
            await db.commit()

    return {"message": "Data added successfully", "store_information_id": info.store_information_id}


# 修改门店信息和月度动态信息接口
@app.post("/update_store_and_monthly_info")
async def update_store_and_monthly_info(info: StoreAndMonthlyInfo):
    async with get_db() as db:
        async with db.cursor() as cursor:
            # 更新门店信息
            update_store_sql = """
            UPDATE 门店信息表 
            SET 客户分类 = %s, 门店位置 = %s, 资金实力 = %s, 门店教练数量 = %s, 会员质量 = %s, 会员数量 = %s, 
                门店课时费或者卡价 = %s, 对运动营养食品熟悉程度 = %s, 主观态度 = %s, 配合程度 = %s, 场景 = %s
            WHERE 店铺信息id = %s
            """
            store_values = (
                info.customer_category, info.store_location, info.financial_strength, info.coach_count,
                info.member_quality, info.member_count, info.class_fee_or_card_price,
                info.familiarity_with_sports_nutrition, info.subjective_attitude, info.cooperation_level, info.scene,
                info.store_information_id
            )
            await cursor.execute(update_store_sql, store_values)

            # 更新月度动态信息
            update_monthly_sql = """
            UPDATE 门店信息月度动态信息表 
            SET 月份 = %s, 销售数量 = %s, 主要销售产品 = %s, 每月动作落实情况 = %s, 动销分析 = %s
            WHERE 店铺信息id = %s AND 月份 = %s
            """
            monthly_values = (
                info.month + '-01', info.sales_quantity, info.main_sales_products,
                info.monthly_action_implementation, info.sales_analysis, info.store_information_id, info.month + '-01'
            )
            await cursor.execute(update_monthly_sql, monthly_values)
            await db.commit()

    return {"message": "Data updated successfully"}


# 查询门店信息和月度动态信息接口
@app.get("/get_store_and_monthly_info/{store_information_id}")
async def get_store_and_monthly_info(store_information_id: int, month: str):
    print(f'接到了前端的请求:store_information_id:{store_information_id},month:{month}')
    async with get_db() as db:
        async with db.cursor(aiomysql.DictCursor) as cursor:
            month = month + '-01'
            # 查询门店信息
            select_store_sql = "SELECT * FROM 门店信息表 WHERE 店铺信息id = %s"
            await cursor.execute(select_store_sql, (store_information_id,))
            store_info = await cursor.fetchone()

            if store_info is None:
                print('没有找到门店信息')

            # 查询月度动态信息
            select_monthly_sql = "SELECT * FROM 门店信息月度动态信息表 WHERE 店铺信息id = %s AND 月份 = %s"
            await cursor.execute(select_monthly_sql, (store_information_id, month))
            monthly_info = await cursor.fetchone()

            if monthly_info is None:
                print('没有找到月度动态信息')

            # 合并结果
            result = {
                "store_info": store_info,
                "monthly_info": monthly_info
            }
            return result


# 门店拜访情况填报接口========================================================================================================================================================================================================
# 定义门店拜访情况填报表模型
class StoreVisitReport(BaseModel):
    id: Optional[int] = None  # 主键id
    store_info_id: Optional[int] = None  # 店铺信息id
    contract_status: Optional[str] = None  # 签约情况
    studio_name: Optional[str] = None  # 工作室名称
    responsible_person: Optional[str] = None  # 负责人
    contact_info: Optional[str] = None  # 联系人方式
    studio_address: Optional[str] = None  # 工作室地址
    sales_person: Optional[str] = None  # 负责业务员
    store_type: Optional[str] = None  # 门店类型
    product_flow: Optional[str] = None  # 产品流向
    private_member_count: Optional[str] = None  # 私教会员数量
    coach_count: Optional[str] = None  # 教练数量
    customer_price: Optional[str] = None  # 客单价
    gym_area: Optional[str] = None  # 健身房面积
    store_highlights: Optional[str] = None  # 门店亮点
    sales_action1: Optional[str] = None  # 提高销量动作1
    sales_action2: Optional[str] = None  # 提高销量动作2
    sales_action3: Optional[str] = None  # 提高销量动作3
    sponsorship_status: Optional[str] = None  # 赞助发放情况
    sales_advantage_reason: Optional[str] = None  # 门店卖货优势原因
    sales_disadvantage_reason: Optional[str] = None  # 门店卖货劣势原因


# 添加门店拜访情况填报表接口
@app.post("/add_store_visit_report")
async def add_store_visit_report(report: StoreVisitReport):
    print('接到了新增的请求')
    async with get_db() as db:
        async with db.cursor() as cursor:
            insert_sql = """INSERT INTO 门店拜访情况填报表 
            (店铺信息id, 签约情况, 工作室名称, 负责人, 联系人方式, 工作室地址, 负责业务员, 门店类型, 产品流向, 私教会员数量, 教练数量, 客单价, 健身房面积, 门店亮点, 提高销量动作1, 提高销量动作2, 提高销量动作3, 赞助发放情况, 门店卖货优势原因, 门店卖货劣势原因) 
            VALUES
            (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            values = (
                convert_empty_to_none(report.store_info_id),
                convert_empty_to_none(report.contract_status),
                convert_empty_to_none(report.studio_name),
                convert_empty_to_none(report.responsible_person),
                convert_empty_to_none(report.contact_info),
                convert_empty_to_none(report.studio_address),
                convert_empty_to_none(report.sales_person),
                convert_empty_to_none(report.store_type),
                convert_empty_to_none(report.product_flow),
                convert_empty_to_none(report.private_member_count),
                convert_empty_to_none(report.coach_count),
                convert_empty_to_none(report.customer_price),
                convert_empty_to_none(report.gym_area),
                convert_empty_to_none(report.store_highlights),
                convert_empty_to_none(report.sales_action1),
                convert_empty_to_none(report.sales_action2),
                convert_empty_to_none(report.sales_action3),
                convert_empty_to_none(report.sponsorship_status),
                convert_empty_to_none(report.sales_advantage_reason),
                convert_empty_to_none(report.sales_disadvantage_reason)
            )
            log_sql = insert_sql % values
            print(log_sql)
            try:
                await cursor.execute(insert_sql, values)
                await db.commit()
            except Exception as e:
                print(e)
                raise HTTPException(status_code=500, detail=str(e))

    return {"message": "Data added successfully"}


# 修改门店拜访情况填报表接口
@app.post("/update_store_visit_report")
async def update_store_visit_report(report: StoreVisitReport):
    store_info_id = report.store_info_id
    async with get_db() as db:
        async with db.cursor() as cursor:
            print('接到了修改的请求')
            set_clauses = []
            values = []

            field_mapping = {
                "id": "id",
                "store_info_id": "店铺信息id",
                "contract_status": "签约情况",
                "studio_name": "工作室名称",
                "responsible_person": "负责人",
                "contact_info": "联系人方式",
                "studio_address": "工作室地址",
                "sales_person": "负责业务员",
                "store_type": "门店类型",
                "product_flow": "产品流向",
                "private_member_count": "私教会员数量",
                "coach_count": "教练数量",
                "customer_price": "客单价",
                "gym_area": "健身房面积",
                "store_highlights": "门店亮点",
                "sales_action1": "提高销量动作1",
                "sales_action2": "提高销量动作2",
                "sales_action3": "提高销量动作3",
                "sponsorship_status": "赞助发放情况",
                "sales_advantage_reason": "门店卖货优势原因",
                "sales_disadvantage_reason": "门店卖货劣势原因"
            }

            for field, value in report.dict().items():
                if value is not None:
                    set_clauses.append(f"{field_mapping[field]} = %s")
                    values.append(convert_empty_to_none(value))

            if not set_clauses:
                raise HTTPException(status_code=400, detail="No valid fields to update")

            update_sql = f"""
            UPDATE 门店拜访情况填报表
            SET {', '.join(set_clauses)}
            WHERE 店铺信息id = %s
            """
            values.append(store_info_id)

            try:
                await cursor.execute(update_sql, values)
                await db.commit()
                if cursor.rowcount == 0:
                    raise HTTPException(status_code=404, detail="Record not found")
            except Exception as e:
                print(e)
                raise HTTPException(status_code=500, detail=str(e))
    return {"message": "Data updated successfully"}


# 查询门店拜访情况填报表接口
@app.get("/get_store_visit_report/{store_id}")
async def get_store_visit_report(store_id: int):
    async with get_db() as db:
        async with db.cursor(aiomysql.DictCursor) as cursor:
            select_sql = "SELECT * FROM 门店拜访情况填报表 WHERE 店铺信息id = %s"
            await cursor.execute(select_sql, (store_id,))
            result = await cursor.fetchone()

            if result is None:
                return None
            result = {
                "id": result["id"],
                "store_info_id": result["店铺信息id"],
                "contract_status": result["签约情况"],
                "studio_name": result["工作室名称"],
                "responsible_person": result["负责人"],
                "contact_info": result["联系人方式"],
                "studio_address": result["工作室地址"],
                "sales_person": result["负责业务员"],
                "store_type": result["门店类型"],
                "product_flow": result["产品流向"],
                "private_member_count": result["私教会员数量"],
                "coach_count": result["教练数量"],
                "customer_price": result["客单价"],
                "gym_area": result["健身房面积"],
                "store_highlights": result["门店亮点"],
                "sales_action1": result["提高销量动作1"],
                "sales_action2": result["提高销量动作2"],
                "sales_action3": result["提高销量动作3"],
                "sponsorship_status": result["赞助发放情况"],
                "sales_advantage_reason": result["门店卖货优势原因"],
                "sales_disadvantage_reason": result["门店卖货劣势原因"]
            }
            return result


# 门店拜访情况详情填报接口========================================================================================================================================================================================================
class StoreVisitDetailReport(BaseModel):
    id: Optional[int] = None
    store_info_id: Optional[int] = None
    activity_implant: Optional[str] = None  # 活动植入
    contract_pyq: Optional[str] = None  # 签约合作发pyq
    sponsor_pyq: Optional[str] = None  # 赞助到店发pyq
    license_pyq: Optional[str] = None  # 授牌到店发pyq
    rollup_banner: Optional[str] = None  # 投放易拉宝
    showcase: Optional[str] = None  # 投放展柜
    daily_cpyq_forward: Optional[str] = None  # 日常pyq转发康比特次数
    add_manager_wechat: Optional[str] = None  # 加负责人微信
    add_coach_wechat: Optional[str] = None  # 加教练微信
    coach_group: Optional[str] = None  # 拉教练群
    product_training: Optional[str] = None  # 教练产品培训
    profit_distribution: Optional[str] = None  # 利润分配
    trial_users: Optional[str] = None  # 试用装体验人数
    trial_drinks: Optional[str] = None  # 试饮次数


# 添加详情接口
@app.post("/add_store_visit_detail")
async def add_store_visit_detail(report: StoreVisitDetailReport):
    async with get_db() as db:
        async with db.cursor() as cursor:
            insert_sql = """INSERT INTO `门店拜访情况详情填报表` 
            (店铺信息id, 活动植入, 签约合作发pyq, 赞助到店发pyq, 授牌到店发pyq, 
             投放易拉宝, 投放展柜, 日常pyq转发康比特次数, 加负责人微信, 加教练微信, 
             拉教练群, 教练产品培训, 利润分配, 试用装体验人数, 试饮次数)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            values = (
                report.store_info_id,
                convert_empty_to_none(report.activity_implant),
                convert_empty_to_none(report.contract_pyq),
                convert_empty_to_none(report.sponsor_pyq),
                convert_empty_to_none(report.license_pyq),
                convert_empty_to_none(report.rollup_banner),
                convert_empty_to_none(report.showcase),
                convert_empty_to_none(report.daily_cpyq_forward),
                convert_empty_to_none(report.add_manager_wechat),
                convert_empty_to_none(report.add_coach_wechat),
                convert_empty_to_none(report.coach_group),
                convert_empty_to_none(report.product_training),
                convert_empty_to_none(report.profit_distribution),
                convert_empty_to_none(report.trial_users),
                convert_empty_to_none(report.trial_drinks)
            )
            try:
                await cursor.execute(insert_sql, values)
                await db.commit()
            except Exception as e:
                raise HTTPException(500, detail=str(e))
    return {"message": "Detail added successfully"}


# 更新详情接口
@app.post("/update_store_visit_detail")
async def update_store_visit_detail(report: StoreVisitDetailReport):
    if not report.store_info_id:
        raise HTTPException(400, "store_info_id required")

    async with get_db() as db:
        async with db.cursor() as cursor:
            set_clauses = []
            params = []

            # 字段映射
            field_map = {
                "activity_implant": "活动植入",
                "contract_pyq": "签约合作发pyq",
                "sponsor_pyq": "赞助到店发pyq",
                "license_pyq": "授牌到店发pyq",
                "rollup_banner": "投放易拉宝",
                "showcase": "投放展柜",
                "daily_cpyq_forward": "日常pyq转发康比特次数",
                "add_manager_wechat": "加负责人微信",
                "add_coach_wechat": "加教练微信",
                "coach_group": "拉教练群",
                "product_training": "教练产品培训",
                "profit_distribution": "利润分配",
                "trial_users": "试用装体验人数",
                "trial_drinks": "试饮次数"
            }

            # 构建动态参数
            for field, db_field in field_map.items():
                value = getattr(report, field)
                if value is not None:
                    set_clauses.append(f"{db_field} = %s")
                    params.append(convert_empty_to_none(value))

            if not set_clauses:
                raise HTTPException(400, "No fields to update")

            update_sql = f"""
            UPDATE `门店拜访情况详情填报表` 
            SET {', '.join(set_clauses)} 
            WHERE 店铺信息id = %s
            """
            params.append(report.store_info_id)

            await cursor.execute(update_sql, params)
            if cursor.rowcount == 0:
                raise HTTPException(404, "Detail not found")
            await db.commit()
    return {"message": "Detail updated"}


# 查询详情接口
@app.get("/get_store_visit_detail/{store_id}")
async def get_store_visit_detail(store_id: int):
    async with get_db() as db:
        async with db.cursor(aiomysql.DictCursor) as cursor:
            await cursor.execute(
                "SELECT * FROM `门店拜访情况详情填报表` WHERE 店铺信息id = %s",
                (store_id,)
            )
            result = await cursor.fetchone()
            if not result:
                return None

            return {
                "id": result["id"],
                "store_info_id": result["店铺信息id"],
                "activity_implant": result["活动植入"],
                "contract_pyq": result["签约合作发pyq"],
                "sponsor_pyq": result["赞助到店发pyq"],
                "license_pyq": result["授牌到店发pyq"],
                "rollup_banner": result["投放易拉宝"],
                "showcase": result["投放展柜"],
                "daily_cpyq_forward": result["日常pyq转发康比特次数"],
                "add_manager_wechat": result["加负责人微信"],
                "add_coach_wechat": result["加教练微信"],
                "coach_group": result["拉教练群"],
                "product_training": result["教练产品培训"],
                "profit_distribution": result["利润分配"],
                "trial_users": result["试用装体验人数"],
                "trial_drinks": result["试饮次数"]
            }


# 打卡-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
class VisitAndCheckIn(BaseModel):
    id: Optional[Union[Any, None]] = Field(default_factory=lambda: None)  # ID
    store_id: Optional[Union[Any, None]] = Field(default_factory=lambda: None)  # 店铺ID
    store_images: Optional[Union[List[Any], Any, None]] = Field(default_factory=lambda: None)  # 照片列表
    store_image_1: Optional[Union[Any, None]] = Field(default_factory=lambda: None)  # 门店图片1
    store_image_2: Optional[Union[Any, None]] = Field(default_factory=lambda: None)  # 门店图片2
    store_image_3: Optional[Union[Any, None]] = Field(default_factory=lambda: None)  # 门店图片3
    notes: Optional[Union[Any, None]] = Field(default_factory=lambda: None)  # 备注
    responsibleName: Optional[Union[Any, None]] = Field(default_factory=lambda: None)  # 负责人姓名
    userId: Optional[Union[Any, None]] = Field(default_factory=lambda: None)  # 负责人id


access_key_id = 'LTAI5tNtgEjQiVNRAXRbwzQK'
access_key_secret = 'EcJ7lpXAc6tOPcPoqeWQJfj1VzEsZo'
endpoint = 'oss-rg-china-mainland.aliyuncs.com'
bucket_name = 'qianrenqiandian'

auth = oss2.Auth(access_key_id, access_key_secret)
bucket = oss2.Bucket(auth, endpoint, bucket_name)


# 添加店铺信息接口
@app.post("/add_check_in_record")
async def add_check_in_record(checkInItem: VisitAndCheckIn):
    print('接到了前端的请求')
    today = datetime.today()
    # 格式化日期为 YYYY-MM-DD 格式
    formatted_date = today.strftime("%Y-%m-%d")
    store_image_1, store_image_2, store_image_3 = None, None, None
    if checkInItem.store_images and len(checkInItem.store_images) > 0:
        for index, store_image in enumerate(checkInItem.store_images):
            if index == 0:
                store_image_1 = store_image
            elif index == 1:
                store_image_2 = store_image
            elif index == 2:
                store_image_3 = store_image

    async with get_db() as db:
        async with db.cursor() as cursor:  # 使用异步上下文管理器创建游标
            insert_sql = """INSERT INTO 打卡记录表 
            (店铺信息id, 打卡图片1, 打卡图片2, 打卡图片3, 备注,打卡日期,打卡人,打卡人id) 
            VALUES
             (%s, %s, %s,%s, %s,%s, %s,%s)
                """
            values = (
                convert_empty_to_none(checkInItem.store_id),  # 店铺ID
                store_image_1,  # 门店图片1
                store_image_2,  # 门店图片2
                store_image_3,  # 门店图片3
                convert_empty_to_none(checkInItem.notes),  # 备注
                formatted_date,  # 今天日期
                convert_empty_to_none(checkInItem.responsibleName),  # 打卡人
                convert_empty_to_none(checkInItem.userId),  # 打卡人id
            )
            log_sql = insert_sql % values
            print(log_sql)
            try:
                await cursor.execute(insert_sql, values)
                await db.commit()  # 提交事务
            except Exception as e:  # 如果插入失败，抛出 HTTP 500 错误并返回异常信息
                print(e)
                raise HTTPException(status_code=500, detail=str(e))

    return {"message": "打卡记录添加成功"}  # 返回打卡记录添加成功的消息


# 查询本人该店铺打卡记录接口
@app.get("/query_check/{id}")
async def query_check(id: int, page: int = 1, page_size: int = 10):
    async with get_db() as db:
        async with db.cursor(aiomysql.DictCursor) as cursor:
            # 计算偏移量
            offset = (page - 1) * page_size

            # 查询总记录数
            count_sql = """
            SELECT COUNT(*) as total_count
            FROM 打卡记录表 
            WHERE 店铺信息id = %s
            """
            try:
                await cursor.execute(count_sql, (id,))
                total_count_result = await cursor.fetchone()
                total_count = total_count_result['total_count']
            except Exception as e:
                raise HTTPException(status_code=500, detail=str(e))

            # 计算总页数
            total_pages = (total_count + page_size - 1) // page_size

            # 分页查询数据
            query_sql = """
            SELECT id,备注,打卡图片1, 打卡图片2, 打卡图片3, 打卡日期,打卡人
            FROM 打卡记录表 
            WHERE 店铺信息id = %s order by 打卡日期 desc
            LIMIT %s OFFSET %s
            """
            log_sql = query_sql % (id, page_size, offset)
            print(log_sql)
            try:
                await cursor.execute(query_sql, (id, page_size, offset))
                results = await cursor.fetchall()
                for row in results:
                    if not row['打卡图片1']:
                        row[
                            '打卡图片1'] = "https://qianrenqiandian.oss-rg-china-mainland.aliyuncs.com/63726f53-abaa-40ab-8d1b-5c244cc11de7.jpg"
            except Exception as e:
                raise HTTPException(status_code=500, detail=str(e))

    # 封装分页信息和查询结果
    response_data = {
        "data": results,
        "page": page,
        "page_size": page_size,
        "total_pages": total_pages,
        "total_count": total_count
    }
    return response_data


@app.delete("/delete_records/{id}")
async def delete_store(id: int):
    print(id)
    async with get_db() as db:
        async with db.cursor() as cursor:
            delete_sql = "DELETE FROM 打卡记录表 WHERE id = %s"
            await cursor.execute(delete_sql, (id,))
            await db.commit()
            return {"message": "删除成功"}


# 查询今日打卡记录接口
@app.get("/query_today_records/{id}")
async def query_today_records(id: int, date: str, page: int = 1, page_size: int = 10):
    print(date)
    async with get_db() as db:
        async with db.cursor(aiomysql.DictCursor) as cursor:
            # 计算偏移量
            offset = (page - 1) * page_size

            # 查询总记录数
            count_sql = """
            SELECT COUNT(*) as total_count
            FROM 打卡记录表 
            WHERE 打卡人id = %s and 打卡日期=%s
            """
            try:
                await cursor.execute(count_sql, (id, date))
                print(count_sql % (id, date))
                total_count_result = await cursor.fetchone()
                total_count = total_count_result['total_count']
            except Exception as e:
                raise HTTPException(status_code=500, detail=str(e))

            # 计算总页数
            total_pages = (total_count + page_size - 1) // page_size
            print(f'总页数为{total_pages}')

            # 分页查询数据
            query_sql = """
            SELECT 打卡图片1,打卡图片2,打卡图片3,门店名称, 备注
            FROM 打卡记录表 a join 店铺信息表 b on a.店铺信息id=b.id
            WHERE 打卡人id = %s and 打卡日期=%s order by a.创建时间 desc
            LIMIT %s OFFSET %s
            """
            try:
                print(query_sql % (id, date, page_size, offset))
                await cursor.execute(query_sql, (id, date, page_size, offset))
                print('查询成功?')
                results = await cursor.fetchall()
                for row in results:
                    if not row['打卡图片1']:
                        row[
                            '打卡图片1'] = "https://qianrenqiandian.oss-rg-china-mainland.aliyuncs.com/63726f53-abaa-40ab-8d1b-5c244cc11de7.jpg"
            except Exception as e:
                print(e)

    # 封装分页信息和查询结果
    response_data = {
        "data": results,
        "page": page,
        "page_size": page_size,
        "total_pages": total_pages,
        "total_count": total_count
    }
    return response_data


if __name__ == "__main__":
    # uvicorn.run("qianrenqiandian2:app", host="0.0.0.0", port=8007)
    uvicorn.run("qianrenqiandian2:app", host="0.0.0.0", port=8007, workers=1, ssl_keyfile="nginx.key",ssl_certfile="nginx.crt")
